﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;

namespace dbschemaupdater
{
    class Generator
    {
        public static void MakeScript(System.IO.TextWriter writer, Model.Table[] model, string connectionString)
        {
            // TODO: optionally ignore collations

            using (var db = new DAL(connectionString))
            {
                var dstTables = db.TABLES.ToArray();
                var dstColumns = db.COLUMNS.ToArray();

                // update existing tables
                foreach (var srcTable in model)
                {
                    var dstTable = dstTables.FirstOrDefault(t => TableNameEqual(t, srcTable.table));
                    if (dstTable == null)
                    {
                        // create missing table
                        writer.WriteLine("create table [{0}].[{1}] (", srcTable.table.SCHEMA, srcTable.table.NAME);
                        bool first = true;
                        foreach (var srcColumn in srcTable.columns)
                        {
                            if (!first)
                                writer.Write(',');
                            else
                                first = false;

                            // TODO: calculated fields
                            writer.WriteLine("\t[{0}] {1}", srcColumn.COLUMN_NAME, FormatDataType(srcColumn));
                        }

                        // create PK
                        var srcPK = srcTable.columns.Where(c => c.PrimaryKey.HasValue).OrderBy(c => c.PrimaryKey.Value);
                        if (srcPK.Any())
                        {
                            writer.Write(",\tprimary key (");
                            writer.Write(string.Join(",", srcPK.Select(c => "[" + c.COLUMN_NAME + "]").ToArray()));
                            writer.WriteLine(")");
                        }

                        // TODO: create check constraints
                        // TODO: create default constraints
                        // TODO: create unique constraints
                        // TODO: create indexes
                        // TODO: support clustered/unclustered

                        writer.WriteLine(")\ngo\n");
                    }
                    else
                    {
                        // table exists, check structure
                        foreach (var srcColumn in srcTable.columns)
                        {
                            var dstColumn = dstColumns.FirstOrDefault(c => ColumnNameEqual(srcColumn, c));
                            if (dstColumn == null)
                            {
                                // create missing column
                                writer.Write("alter table [{0}].[{1}]\nadd [{2}] {3}", srcColumn.TABLE_SCHEMA, srcColumn.TABLE_NAME, srcColumn.COLUMN_NAME, FormatDataType(srcColumn));
                                writer.WriteLine("\ngo\n");
                            }
                            else if (srcColumn.DATA_TYPE != dstColumn.DATA_TYPE
                                || srcColumn.CHARACTER_MAXIMUM_LENGTH != dstColumn.CHARACTER_MAXIMUM_LENGTH
                                || srcColumn.DATETIME_PRECISION != dstColumn.DATETIME_PRECISION
                                || srcColumn.NUMERIC_PRECISION != dstColumn.NUMERIC_PRECISION
                                || srcColumn.NUMERIC_SCALE != dstColumn.NUMERIC_SCALE
                                || srcColumn.IS_NULLABLE != dstColumn.IS_NULLABLE)
                            {
                                // update changed columns
                                writer.Write("alter table [{0}].[{1}]\nalter column [{2}] {3}", srcColumn.TABLE_SCHEMA, srcColumn.TABLE_NAME, srcColumn.COLUMN_NAME, FormatDataType(srcColumn));
                                writer.WriteLine("\ngo\n");
                            }
                        }

                        var srcPK = srcTable.columns.Where(c => c.PrimaryKey.HasValue).OrderBy(c => c.PrimaryKey.Value);
                        var dstPK = dstColumns.Where(c => c.PrimaryKey.HasValue && TableNameEqual(dstTable, c)).OrderBy(c => c.PrimaryKey.Value);

                        if (!srcPK.SequenceEqual(dstPK, new ColumnNameEqualityComparer()))
                        {
                            //TODO: update PK (drop PK, create PK)

                        }
                    }
                }

                // TODO: compare & create foreign keys
            }
        }

        private static bool TableNameEqual(DAL.TABLE tbl1, DAL.TABLE tbl2)
        {
            return string.Equals(tbl1.NAME, tbl2.NAME, StringComparison.InvariantCultureIgnoreCase) && string.Equals(tbl1.SCHEMA, tbl2.SCHEMA, StringComparison.InvariantCultureIgnoreCase);
        }

        private static bool TableNameEqual(DAL.TABLE tbl, DAL.COLUMN col)
        {
            return string.Equals(tbl.NAME, col.TABLE_NAME, StringComparison.InvariantCultureIgnoreCase) && string.Equals(tbl.SCHEMA, col.TABLE_SCHEMA, StringComparison.InvariantCultureIgnoreCase);
        }

        private static bool ColumnNameEqual(DAL.COLUMN col1, DAL.COLUMN col)
        {
            return string.Equals(col1.TABLE_NAME, col.TABLE_NAME, StringComparison.InvariantCultureIgnoreCase)
                && string.Equals(col1.TABLE_SCHEMA, col.TABLE_SCHEMA, StringComparison.InvariantCultureIgnoreCase)
                && string.Equals(col1.COLUMN_NAME, col.COLUMN_NAME, StringComparison.InvariantCultureIgnoreCase);
        }

        private class ColumnNameEqualityComparer : IEqualityComparer<DAL.COLUMN>
        {
            #region IEqualityComparer<COLUMN> Members

            public bool Equals(DAL.COLUMN x, DAL.COLUMN y)
            {
                return ColumnNameEqual(x, y);
            }

            public int GetHashCode(DAL.COLUMN obj)
            {
                return obj.TABLE_NAME.GetHashCode() ^ obj.TABLE_SCHEMA.GetHashCode() ^ obj.COLUMN_NAME.GetHashCode();
            }

            #endregion
        }

        private static string FormatDataType(DAL.COLUMN col)
        {
            string dataType = col.DATA_TYPE.ToLowerInvariant();
            string format, result;
            if (!_dataTypeFormats.TryGetValue(dataType, out format))
                result = dataType;
            else
            {
                string charlen = col.CHARACTER_MAXIMUM_LENGTH.HasValue && col.CHARACTER_MAXIMUM_LENGTH.Value < 0 ? "max" : col.CHARACTER_MAXIMUM_LENGTH.ToString();
                result = string.Format(format, charlen, col.NUMERIC_PRECISION, col.NUMERIC_SCALE, col.DATETIME_PRECISION);
            }

            if (!string.IsNullOrEmpty(col.COLLATION_NAME))
                result += " collate " + col.COLLATION_NAME;

            if (string.Equals(col.IS_NULLABLE, "NO", StringComparison.InvariantCultureIgnoreCase))
                result += " not null";

            if (col.IsIdentity == 1)
                result += string.Format(" identity({0},{1})", col.IdentSeed ?? 1, col.IdentIncr ?? 1);

            return result;
        }


        /// <summary>
        /// Data type formats
        /// {0} CHARACTER_MAXIMUM_LENGTH
        /// {1} NUMERIC_PRECISION
        /// {2} NUMERIC_SCALE
        /// {3} DATETIME_PRECISION
        /// </summary>
        private static readonly Dictionary<string, string> _dataTypeFormats;

        static Generator()
        {
            _dataTypeFormats = new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase);
            _dataTypeFormats.Add("char", "char({0})");
            _dataTypeFormats.Add("nchar", "nchar({0})");
            _dataTypeFormats.Add("varchar", "varchar({0})");
            _dataTypeFormats.Add("nvarchar", "nvarchar({0})");
            _dataTypeFormats.Add("binary", "binary({0})");
            _dataTypeFormats.Add("varbinary", "varbinary({0})");

            _dataTypeFormats.Add("numeric", "numeric({1},{2})");
            _dataTypeFormats.Add("decimal", "decimal({1},{2})");
            _dataTypeFormats.Add("datetimeoffset", "datetimeoffset({3})");
            _dataTypeFormats.Add("datetime2", "datetime2({3})");
            _dataTypeFormats.Add("time", "time({3})");
        }
    }
}
